/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Connections;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.Vector;

/**
 *
 * @author BachDEV
 */
public class DataTools {
    Connection cnn = null;
    CallableStatement cstmt = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int n = 0;
    public Vector getOrders() throws Exception
    {
        Vector vec = new Vector();
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{Call sp_showOrder}");
            rs = cstmt.executeQuery();
            while(rs.next())
            {
                Vector ve = new Vector();
                ve.add(rs.getInt(1));
                ve.add(rs.getObject(2));
                String[] arr = rs.getString(3).split(" ");
                ve.add(arr[0]);
                ve.add(rs.getObject(4));
                ve.add(rs.getObject(5));
                ve.add(rs.getObject(6));
                if(rs.getInt(7) == 1)
                    ve.add("Paid");
                else
                    ve.add("Unpaid");
                if(rs.getInt(8) == 1)
                {
                    ve.add("Delivered");
                }
                else
                    ve.add("Not delivered");
                vec.add(ve);
            }
        }
        catch(Exception ex)
        {
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, cstmt, rs);
        }
        return vec;
    }

    public Vector getProduct() throws Exception
    {
        Vector vec = new Vector();
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_showProduct}");
            rs = cstmt.executeQuery();
            while(rs.next())
            {
                Vector ve = new Vector();
                ve.add(rs.getInt(1));
                ve.add(rs.getString(2));
                ve.add(rs.getString(3));
                ve.add(rs.getString(4));
                ve.add(rs.getString(5));
                ve.add(rs.getString(6));
                ve.add(rs.getString(7));
                vec.add(ve);
            }
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }

        return vec;
    }

    public Vector getCustormer() throws Exception
    {
        Vector cus = new Vector();
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            String sqlCus = "Select * from Customer";
            stmt = cnn.prepareStatement(sqlCus);
            rs = stmt.executeQuery();
            while(rs.next())
            {
                Vector vec = new Vector();
                vec.add(rs.getInt(1));
                vec.add(rs.getString(2));
                vec.add(rs.getString(3));
                vec.add(rs.getString(4));
                vec.add(rs.getString(5));
                if(rs.getInt(6) == 1)
                    vec.add("Show");
                else
                    vec.add("Hide");
                vec.add(rs.getString(7));
                cus.add(vec);
            }
        }
        catch(Exception ex)
        {
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return cus;
    }

    public Vector searchCus(String str, int n) throws Exception
    {
        Vector cus = new Vector();
        try
        {
                cnn = DBConnection.getConnection("eProject", "sa", "123");
                if(n == 1)
                {
                    cstmt = cnn.prepareCall("{call sp_searchCusPhone(?)}");
                    cstmt.setObject(1, str, Types.VARCHAR);
                }
                else if(n ==2)
                {
                    cstmt = cnn.prepareCall("{call sp_searchCusEmail(?)}");
                    cstmt.setObject(1, str, Types.VARCHAR);
                }
                else
                {
                    cstmt = cnn.prepareCall("{call sp_searchCusDispatch(?)}");
                    cstmt.setObject(1, str, Types.VARCHAR);
                }
                rs = cstmt.executeQuery();
                while(rs.next())
                {
                    Vector ve = new Vector();
                    ve.add(rs.getString(1));
                    ve.add(rs.getString(2));
                    ve.add(rs.getString(3));
                    ve.add(rs.getString(4));
                    ve.add(rs.getString(5));
                    ve.add(rs.getString(6));
                    ve.add(rs.getString(7));
                    cus.add(ve);
                }

        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return cus;
    }

    public int updateOrder(Vector vec) throws Exception
    {
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_updateOrder(?, ?, ?, ?, ?, ?, ?)}");
            cstmt.setInt(1, Integer.parseInt(vec.get(7).toString()));
            cstmt.setInt(2, Integer.parseInt(vec.get(8).toString()));
            System.out.println("Date: " + vec.get(0));
            cstmt.setObject(3, vec.get(0), Types.VARCHAR);
            cstmt.setObject(4, vec.get(1), Types.VARCHAR);
            cstmt.setObject(5, vec.get(2), Types.VARCHAR);
            cstmt.setObject(6, vec.get(3), Types.VARCHAR);
            cstmt.setObject(7, vec.get(4), Types.VARCHAR);
            cstmt.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return n;
    }

    public void addOrder(Vector vec) throws Exception
    {
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_addOrders(?, ?, ?, ?, ?, ?, ?)}");
            cstmt.setInt(1, Integer.parseInt(vec.get(7).toString()));
            System.out.println("Date add: " + vec.get(0));
            cstmt.setObject(2, vec.get(0), Types.VARCHAR);
            cstmt.setObject(3, vec.get(1), Types.VARCHAR);
            cstmt.setObject(4, vec.get(2), Types.VARCHAR);
            cstmt.setObject(5, vec.get(3), Types.VARCHAR);
            cstmt.setObject(6, vec.get(4), Types.VARCHAR);
            cstmt.setString(7, "False");
            cstmt.executeUpdate();
        }
        catch(Exception ex)
        {
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
    }

    public void addOrderDetails(Vector vec) throws Exception
    {
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_addOrderDetail(?, ?)}");
            cstmt.setInt(1, Integer.parseInt(vec.get(5).toString()));
            cstmt.setInt(2, Integer.parseInt(vec.get(6).toString()));
            cstmt.executeUpdate();
        }
        catch(Exception ex)
        {
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
    }

    public void updateOrderDetails(Vector vec) throws Exception
    {
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_updateOrderDetail(?, ?, ?)}");
            cstmt.setInt(1, Integer.parseInt(vec.get(7).toString()));
            cstmt.setString(2, vec.get(5).toString());
            cstmt.setString(3, vec.get(6).toString());
            cstmt.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
    }

    public int updateCus(Vector vec) throws Exception
    {
        int i;
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_updateCus(?, ?, ?, ?, ?, ?, ?)}");
            cstmt.setInt(1, Integer.parseInt(vec.get(6).toString()));
            cstmt.setObject(2, vec.get(0), Types.VARCHAR);
            cstmt.setObject(3, vec.get(1), Types.VARCHAR);
            cstmt.setObject(4, vec.get(2), Types.VARCHAR);
            cstmt.setObject(5, vec.get(3), Types.VARCHAR);
            cstmt.setObject(6, vec.get(4), Types.VARCHAR);
            cstmt.setObject(7, vec.get(5), Types.VARCHAR);
            i = cstmt.executeUpdate();
        }
        catch(Exception ex)
        {
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return i;
    }

    public int addCus(Vector vec) throws Exception
    {
        int n;
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_addCus(?, ?, ?, ?, ?, ?)}");
            cstmt.setObject(1, vec.get(0), Types.VARCHAR);
            cstmt.setObject(2, vec.get(1), Types.VARCHAR);
            cstmt.setObject(3, vec.get(2), Types.VARCHAR);
            cstmt.setObject(4, vec.get(3), Types.VARCHAR);
            cstmt.setObject(5, vec.get(4), Types.VARCHAR);
            cstmt.setObject(6, vec.get(5), Types.VARCHAR);
            n = cstmt.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return n;
    }

    public Vector searchOrder(String str, int n) throws Exception
    {
        Vector vec = new Vector();
        System.out.println("Str: " + str);
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            if(n == 1)
            {
                cstmt = cnn.prepareCall("{call sp_searchOrdName(?)}");
            }
            else if (n == 2)
            {
                cstmt = cnn.prepareCall("{call sp_searchOrdPhone(?)}");
            }
            else
            {
                cstmt = cnn.prepareCall("{call sp_searchOrdDate(?)}");
            }
            cstmt.setObject(1, str, Types.VARCHAR);
            rs = cstmt.executeQuery();
                while(rs.next())
                {
                    Vector ve = new Vector();
                    ve.add(rs.getInt(1));
                    ve.add(rs.getString(2));
                    ve.add(rs.getString(3));
                    ve.add(rs.getString(4));
                    ve.add(rs.getString(5));
                    ve.add(rs.getString(6));
                    if(rs.getInt(7) == 1)
                        ve.add("Checked");
                    else
                        ve.add("Uncheck");
                    if(rs.getInt(8) == 1)
                        ve.add("Dispatched");
                    else
                        ve.add("Undispatch");
                    vec.add(ve);
                }
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return vec;
    }

    public Vector searchDateSE(String str1, String str2) throws Exception
    {
        Vector vec = new Vector();
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_searchDateStartEnd(?, ?)}");
            cstmt.setObject(1, str1);
            cstmt.setObject(2, str2);
            rs = cstmt.executeQuery();
            while(rs.next())
            {
                Vector ve = new Vector();
                ve.add(rs.getInt(1));
                ve.add(rs.getString(2));
                ve.add(rs.getString(3));
                ve.add(rs.getString(4));
                ve.add(rs.getString(5));
                ve.add(rs.getString(6));
                if(rs.getInt(7) == 1)
                    ve.add("Paid");
                else
                    ve.add("UnPaid");
                if(rs.getInt(8) == 1)
                    ve.add("Delivered");
                else
                    ve.add("Not Delivered");
                vec.add(ve);
            }
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }

        return vec;
    }

    public Vector getComplain() throws Exception
    {
        Vector vec =  new Vector();
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            cstmt = cnn.prepareCall("{call sp_getComplain}");
            rs = cstmt.executeQuery();
            while(rs.next())
            {
                Vector ve = new Vector();
                ve.add(rs.getInt(1));
                ve.add(rs.getString(2));
                ve.add(rs.getString(3));
                ve.add(rs.getString(4));
                ve.add(rs.getString(5));
                ve.add(rs.getString(6));
                if(rs.getInt(7) == 1)
                {
                    ve.add("Fixed");
                }
                else
                    ve.add("Unfix");
                vec.add(ve);
            }
        }
        catch(Exception ex)
        {
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return vec;
    }

    public int updateComp(Vector vec) throws Exception
    {
        int n;
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            if("Unfix".equalsIgnoreCase(vec.get(2).toString()))
            {
                cstmt = cnn.prepareCall("{call sp_updateComp(?, ?, ?)}");
                cstmt.setInt(1, Integer.parseInt(vec.get(0).toString()));
                cstmt.setString(2, vec.get(1).toString());
                cstmt.setInt(3, 1);
            }
            else
            {
                cstmt = cnn.prepareCall("{call sp_updateComp1(?, ?)}");
                cstmt.setInt(1, Integer.parseInt(vec.get(0).toString()));
                cstmt.setInt(2, 0);
            }
            n = cstmt.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return n;
    }

    public Vector searchComp(Vector vec) throws Exception
    {
        Vector ve = new Vector();
        try
        {
            cnn = DBConnection.getConnection("eProject", "sa", "123");
            if("Date receive".equalsIgnoreCase(vec.get(0).toString()))
            {
                cstmt = cnn.prepareCall("{call sp_SearchByDateReceive(?, ?)}");
                cstmt.setString(1, vec.get(1).toString());
                cstmt.setString(2,vec.get(2).toString());
            }
            else
            {
                cstmt = cnn.prepareCall("{call sp_SearchByDateFixed(?, ?)}");
                cstmt.setString(1, vec.get(1).toString());
                cstmt.setString(2, vec.get(2).toString());
            }
            rs = cstmt.executeQuery();
            while(rs.next())
            {
                Vector v = new Vector();
                v.add(rs.getInt(1));
                v.add(rs.getString(2));
                v.add(rs.getString(3));
                v.add(rs.getString(4));
                v.add(rs.getString(5));
                v.add(rs.getString(6));
                if(rs.getInt(7) == 1)
                {
                    v.add("Fixed");
                }
                else
                {
                    v.add("Unfixed");
                }
                ve.add(v);
            }
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
        finally
        {
            DBConnection.closeConnection(cnn, stmt, rs);
        }
        return ve;
    }
}

